﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;

namespace OrmSmackdown.AdoNet
{
    public class Aggregate : IAggregate
    {
        string _connectionString;
        SqlCommand _cmd;

        public Aggregate()
        {
            _cmd = new SqlCommand();

            _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
        }

        public static void ClearAggregateCounts()
        {
            var dsn = System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
            using (var cmd = new SqlCommand())
            {
                using (var cn = new SqlConnection(dsn))
                {
                    cn.Open();
                    cmd.Connection = cn;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@action", Constants.STR_AggregateCount);
                    cmd.CommandText = "delete from Performance where Action = @action";
                    
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void InsertRecordsApproachOne(int recordsToInsert)
        {
            using (var cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                _cmd.Connection = cn;
                _cmd.CommandText = @"
declare @idx as int; set @idx = 1;
while @idx < @recordCount
begin
insert into Performance(OrmName, Action, MilliSeconds)
  values('AdoDotNet',@action,@idx)
  set @idx = @idx + 1
end;";
                _cmd.Parameters.Clear();
                _cmd.Parameters.AddWithValue("@recordCount", recordsToInsert);
                _cmd.Parameters.AddWithValue("@action", Constants.STR_AggregateCount);
                _cmd.ExecuteNonQuery();
                _cmd.Parameters.Clear();
            }
        }

        public void InsertRecordsApproachTwo(int recordsToInsert)
        {
            using (var cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                using (var transaction = cn.BeginTransaction())
                {
                    _cmd.Connection = cn;
                    _cmd.Transaction = transaction;
                    _cmd.CommandText = "insert into performance(OrmName,Action,MilliSeconds) values('ADO.NET',@action,@value)";

                    for (int idx = 0; idx < recordsToInsert; ++idx)
                    {
                        _cmd.Parameters.Clear();
                        _cmd.Parameters.AddWithValue("@value", idx);
                        _cmd.Parameters.AddWithValue("@action", Constants.STR_AggregateCount);
                        _cmd.ExecuteNonQuery();
                    }

                    transaction.Commit();
                }
            }

            //Not using transaction
            //using (var cn = new SqlConnection(_connectionString))
            //{
            //    cn.Open();
            //    _cmd.Connection = cn;
            //    _cmd.CommandText = "insert into performance(OrmName,Action,MilliSeconds) values('ADO.NET','AggregateCounts',@value)";

            //    for (int idx = 0; idx < recordsToInsert; ++idx)
            //    {
            //        _cmd.Parameters.Clear();
            //        _cmd.Parameters.AddWithValue("@value", idx);
            //        _cmd.ExecuteNonQuery();
            //    }
            //}
        }

        public int GetCountApproachOne()
        {
            using (var cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                _cmd.CommandText = "select count(performanceid) from Performance where Action=@action";
                _cmd.Parameters.Clear();
                _cmd.Parameters.AddWithValue("@action", Constants.STR_AggregateCount);
                _cmd.Connection = cn;

                return Convert.ToInt32(_cmd.ExecuteScalar());
            }
        }

        public int GetAverageApproachOne()
        {
            using (var cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                _cmd.CommandText = "select isnull(avg(MilliSeconds),0) from Performance where Action=@action";
                _cmd.Parameters.Clear();
                _cmd.Parameters.AddWithValue("@action", Constants.STR_AggregateCount);
                _cmd.Connection = cn;

                return Convert.ToInt32(_cmd.ExecuteScalar());
            }
        }

        public void DeleteRecordsApproachOne()
        {
            using (var cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                _cmd.Connection = cn;
                _cmd.CommandText = "truncate table Performance";
            }
        }

        public void DeleteRecordsApproachTwo()
        {
            using (var cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                _cmd.Connection = cn;
                _cmd.CommandText = "delete from Performance where Action=@action";
                _cmd.Parameters.Clear();
                _cmd.Parameters.AddWithValue("@action", Constants.STR_AggregateCount);
                _cmd.ExecuteNonQuery();
            }
        }

        public int GetAverageApproachTwo()
        {
            using (var cn = new SqlConnection(_connectionString))
            {
                int count = 0;
                int sum = 0;

                cn.Open();
                _cmd.Connection = cn;

                _cmd.CommandText = "select MilliSeconds from Performance where Action=@action";
                _cmd.Parameters.Clear();
                _cmd.Parameters.AddWithValue("@action", Constants.STR_AggregateCount);

                using (var rdr = _cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        sum += Convert.ToInt32(rdr["MilliSeconds"]);
                        count++;
                    }
                }
                return (count == 0) ? 0 : sum / count;
            }
        }

        public int GetCountApproachTwo()
        {
            using (var cn = new SqlConnection(_connectionString))
            {
                int count = 0;

                cn.Open();
                _cmd.Connection = cn;

                _cmd.CommandText = "select PerformanceId from Performance where Action=@action";
                _cmd.Parameters.Clear();
                _cmd.Parameters.AddWithValue("@action", Constants.STR_AggregateCount);
                
                using (var rdr = _cmd.ExecuteReader())
                {
                    while (rdr.Read())
                        count++;
                }
                return count;
            }
        }

        public void Dispose()
        {
            _cmd.Dispose();
        }
    }
}
